package com.aisilesn.qa.dao;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import com.aisilesn.qa.pojo.Problem;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 * 数据访问接口
 * @author Administrator
 *
 */
public interface ProblemDao extends JpaRepository<Problem,String>,JpaSpecificationExecutor<Problem>{
	// 根据标签ID查询最新问题列表, 查询标签ID => 问题  replyTime 降序
    //@Query(value = "select pr.* from tb_problem pr where pr.id in (select p.problemid from tb_pl p where p.labelid=?1) order by pr.replytime desc",nativeQuery = true)
    @Query("select p from Problem p where p.id in (select pl from Pl pl where pl.labelid=?1) order by p.replytime desc")
    Page<Problem> findNewListByLabelId(String labelId, Pageable pageable);

    //根据标签ID查询热门问题列表 ,回复数量 reply降序排序
    @Query(value = "select p.* from tb_problem p left join tb_pl pr on p.id=pr.problemid WHERE pr.labelid=?1 ORDER by p.reply DESC",nativeQuery = true)
    Page<Problem> findHotListByLabelId(String labelId,Pageable pageable);

    // 根据标签ID查询等待回答列表, 问题创建时间 createtime降序
    @Query(value = "select p.* from tb_problem p left join tb_pl pr on p.id=pr.problemid where pr.labelid=:labelid and reply=0 order by p.createtime desc",nativeQuery = true)
    Page<Problem> findWaitListByLabelid(@Param("labelid") String labelid, Pageable pageable);

}
